using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Common;
using RMAS.DAL;

namespace RMAS.DAL
{
    /// <summary>
    /// Summary description for DM_MENU.
    /// </summary>
    public class DM_MENUDao
    {
        private string _SQLSelectByKey;
        private string _SQLUpdate;
        private string _SQLInsert;
        private string _SQLDelete;
        private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion

        #region Constructor

        public DM_MENUDao()
        {
            _dbConnection = null;
            InitSqlQuery();
        }

        public DM_MENUDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
        {
            string query;

            query = @"SELECT PrID, ID, PrName, PrImage, GroupMenuID, UnitID1, UnitID2, Number, PrVAT, PrMadeIn, PrPrice_Purchase, PrPrice_AfterVAT, PrPrice_RetailSale, PrPrice_WholeSale, Note, UserNameCreate, DateCreate, IDPOS, IsUse, IsDelete FROM DM_MENU WHERE (PrID = @PrID)";
            _SQLSelectByKey = query;

            query = @"UPDATE DM_MENU SET ID = @ID, PrName = @PrName, PrImage = @PrImage, GroupMenuID = @GroupMenuID, UnitID1 = @UnitID1, UnitID2 = @UnitID2, Number = @Number, PrVAT = @PrVAT, PrMadeIn = @PrMadeIn, PrPrice_Purchase = @PrPrice_Purchase, PrPrice_AfterVAT = @PrPrice_AfterVAT, PrPrice_RetailSale = @PrPrice_RetailSale, PrPrice_WholeSale = @PrPrice_WholeSale, Note = @Note, UserNameCreate = @UserNameCreate, DateCreate = @DateCreate, IDPOS = @IDPOS, IsUse = @IsUse, IsDelete = @IsDelete WHERE (PrID = @PrID)";
            _SQLUpdate = query;

            query = @"INSERT INTO DM_MENU VALUES (@PrID, @ID, @PrName, @PrImage, @GroupMenuID, @UnitID1, @UnitID2, @Number, @PrVAT, @PrMadeIn, @PrPrice_Purchase, @PrPrice_AfterVAT, @PrPrice_RetailSale, @PrPrice_WholeSale, @Note, @UserNameCreate, @DateCreate, @IDPOS, @IsUse, @IsDelete)";
            _SQLInsert = query;

            query = @"DELETE FROM DM_MENU WHERE (PrID = @PrID)";
            _SQLDelete = query;

            query = @"SELECT PrID, ID, PrName, PrImage, GroupMenuID, UnitID1, UnitID2, Number, PrVAT, PrMadeIn, PrPrice_Purchase, PrPrice_AfterVAT, PrPrice_RetailSale, PrPrice_WholeSale, Note, UserNameCreate, DateCreate, IDPOS, IsUse, IsDelete FROM DM_MENU";
            _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(DM_MENU obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                DalTools.addDbDataParameter(command, "PrID", obj.PrID, DbType.String);
                DalTools.addDbDataParameter(command, "ID", obj.ID, DbType.String);
                DalTools.addDbDataParameter(command, "PrName", obj.PrName, DbType.String);
                DalTools.addDbDataParameter(command, "PrImage", obj.PrImage, DbType.Binary);
                DalTools.addDbDataParameter(command, "GroupMenuID", obj.GroupMenuID, DbType.String);
                DalTools.addDbDataParameter(command, "UnitID1", obj.UnitID1, DbType.String);
                DalTools.addDbDataParameter(command, "UnitID2", obj.UnitID2, DbType.String);
                DalTools.addDbDataParameter(command, "Number", obj.Number, DbType.Double);
                DalTools.addDbDataParameter(command, "PrVAT", obj.PrVAT, DbType.Double);
                DalTools.addDbDataParameter(command, "PrMadeIn", obj.PrMadeIn, DbType.String);
                DalTools.addDbDataParameter(command, "PrPrice_Purchase", obj.PrPrice_Purchase, DbType.Decimal);
                DalTools.addDbDataParameter(command, "PrPrice_AfterVAT", obj.PrPrice_AfterVAT, DbType.Decimal);
                DalTools.addDbDataParameter(command, "PrPrice_RetailSale", obj.PrPrice_RetailSale, DbType.Decimal);
                DalTools.addDbDataParameter(command, "PrPrice_WholeSale", obj.PrPrice_WholeSale, DbType.Decimal);
                DalTools.addDbDataParameter(command, "Note", obj.Note, DbType.String);
                DalTools.addDbDataParameter(command, "UserNameCreate", obj.UserNameCreate, DbType.String);
                DalTools.addDbDataParameter(command, "DateCreate", obj.DateCreate, DbType.DateTime);
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);
                DalTools.addDbDataParameter(command, "IsUse", obj.IsUse, DbType.Boolean);
                DalTools.addDbDataParameter(command, "IsDelete", obj.IsDelete, DbType.Boolean);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(DM_MENU obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                DalTools.addDbDataParameter(command, "PrID", obj.PrID, DbType.String);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(DM_MENU obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(DM_MENU obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties

        private DM_MENUList SelectListByCommand(IDbCommand command)
        {
            DM_MENUList list = new DM_MENUList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                DM_MENU obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new DM_MENU();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private DM_MENUList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            DM_MENUList list = new DM_MENUList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                DM_MENU obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new DM_MENU();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void ReadProperties(DM_MENU obj, IDataReader reader)
        {
            try
            {
                if (!(reader["PrID"] is DBNull))
                {
                    obj.PrID = (string)reader["PrID"];
                }
                if (!(reader["ID"] is DBNull))
                {
                    obj.ID = (string)reader["ID"];
                }
                if (!(reader["PrName"] is DBNull))
                {
                    obj.PrName = (string)reader["PrName"];
                }
                if (!(reader["PrImage"] is DBNull))
                {
                    obj.PrImage = (byte[])reader["PrImage"];
                }
                if (!(reader["GroupMenuID"] is DBNull))
                {
                    obj.GroupMenuID = (string)reader["GroupMenuID"];
                }
                if (!(reader["UnitID1"] is DBNull))
                {
                    obj.UnitID1 = (string)reader["UnitID1"];
                }
                if (!(reader["UnitID2"] is DBNull))
                {
                    obj.UnitID2 = (string)reader["UnitID2"];
                }
                if (!(reader["Number"] is DBNull))
                {
                    obj.Number = (double)reader["Number"];
                }
                if (!(reader["PrVAT"] is DBNull))
                {
                    obj.PrVAT = (double)reader["PrVAT"];
                }
                if (!(reader["PrMadeIn"] is DBNull))
                {
                    obj.PrMadeIn = (string)reader["PrMadeIn"];
                }
                if (!(reader["PrPrice_Purchase"] is DBNull))
                {
                    obj.PrPrice_Purchase = (decimal)reader["PrPrice_Purchase"];
                }
                if (!(reader["PrPrice_AfterVAT"] is DBNull))
                {
                    obj.PrPrice_AfterVAT = (decimal)reader["PrPrice_AfterVAT"];
                }
                if (!(reader["PrPrice_RetailSale"] is DBNull))
                {
                    obj.PrPrice_RetailSale = (decimal)reader["PrPrice_RetailSale"];
                }
                if (!(reader["PrPrice_WholeSale"] is DBNull))
                {
                    obj.PrPrice_WholeSale = (decimal)reader["PrPrice_WholeSale"];
                }
                if (!(reader["Note"] is DBNull))
                {
                    obj.Note = (string)reader["Note"];
                }
                if (!(reader["UserNameCreate"] is DBNull))
                {
                    obj.UserNameCreate = (string)reader["UserNameCreate"];
                }
                if (!(reader["DateCreate"] is DBNull))
                {
                    obj.DateCreate = (DateTime)reader["DateCreate"];
                }
                if (!(reader["IDPOS"] is DBNull))
                {
                    obj.IDPOS = (string)reader["IDPOS"];
                }
                if (!(reader["IsUse"] is DBNull))
                {
                    obj.IsUse = (bool)reader["IsUse"];
                }
                if (!(reader["IsDelete"] is DBNull))
                {
                    obj.IsDelete = (bool)reader["IsDelete"];
                }
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion

        public DM_MENU SelectByKey(string PrID)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "PrID", PrID, DbType.String);
            DM_MENUList list = SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_MENU SelectByKey(string PrID, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "PrID", PrID, DbType.String);
            DM_MENUList list = SelectListByCommand(command, trans);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_MENUList SelectAll()
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            DM_MENUList list = SelectListByCommand(command);
            return list;
        }
        public DM_MENUList SelectAll(IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            DM_MENUList list = SelectListByCommand(command, trans);
            return list;
        }
        public DM_MENUList SelectByCommand(string Command)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = Command;
            DM_MENUList list = SelectListByCommand(command);
            return list;
        }

        public DM_MENUList SelectByCondition(DM_MENU obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_MENU " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_MENUList list = SelectListByCommand(command, trans);
            return list;
        }

        public DM_MENUList SelectByCondition(DM_MENU obj)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_MENU " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_MENUList list = SelectListByCommand(command);
            return list;
        }
        private void DeleteByCondition(DM_MENU obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM DM_MENU " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(DM_MENU obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            if (obj.IsSetPrID)
            {
                s += " AND PrID = @PrID ";
            }
            if (obj.IsSetID)
            {
                s += " AND ID = @ID ";
            }
            if (obj.IsSetPrName)
            {
                s += " AND PrName = @PrName ";
            }
            if (obj.IsSetPrImage)
            {
                s += " AND PrImage = @PrImage ";
            }
            if (obj.IsSetGroupMenuID)
            {
                s += " AND GroupMenuID = @GroupMenuID ";
            }
            if (obj.IsSetUnitID1)
            {
                s += " AND UnitID1 = @UnitID1 ";
            }
            if (obj.IsSetUnitID2)
            {
                s += " AND UnitID2 = @UnitID2 ";
            }
            if (obj.IsSetNumber)
            {
                s += " AND Number = @Number ";
            }
            if (obj.IsSetPrVAT)
            {
                s += " AND PrVAT = @PrVAT ";
            }
            if (obj.IsSetPrMadeIn)
            {
                s += " AND PrMadeIn = @PrMadeIn ";
            }
            if (obj.IsSetPrPrice_Purchase)
            {
                s += " AND PrPrice_Purchase = @PrPrice_Purchase ";
            }
            if (obj.IsSetPrPrice_AfterVAT)
            {
                s += " AND PrPrice_AfterVAT = @PrPrice_AfterVAT ";
            }
            if (obj.IsSetPrPrice_RetailSale)
            {
                s += " AND PrPrice_RetailSale = @PrPrice_RetailSale ";
            }
            if (obj.IsSetPrPrice_WholeSale)
            {
                s += " AND PrPrice_WholeSale = @PrPrice_WholeSale ";
            }
            if (obj.IsSetNote)
            {
                s += " AND Note = @Note ";
            }
            if (obj.IsSetUserNameCreate)
            {
                s += " AND UserNameCreate = @UserNameCreate ";
            }
            if (obj.IsSetDateCreate)
            {
                s += " AND DateCreate = @DateCreate ";
            }
            if (obj.IsSetIDPOS)
            {
                s += " AND IDPOS = @IDPOS ";
            }
            if (obj.IsSetIsUse)
            {
                s += " AND IsUse = @IsUse ";
            }
            if (obj.IsSetIsDelete)
            {
                s += " AND IsDelete = @IsDelete ";
            }
            if (s.Length > 0)
            {
                s = " WHERE " + s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, DM_MENU obj)
        {
            if (obj == null) { return; }
            if (obj.IsSetPrID)
            {
                DalTools.addDbDataParameter(command, "PrID", obj.PrID, DbType.String);
            }
            if (obj.IsSetID)
            {
                DalTools.addDbDataParameter(command, "ID", obj.ID, DbType.String);
            }
            if (obj.IsSetPrName)
            {
                DalTools.addDbDataParameter(command, "PrName", obj.PrName, DbType.String);
            }
            if (obj.IsSetPrImage)
            {
                DalTools.addDbDataParameter(command, "PrImage", obj.PrImage, DbType.Binary);
            }
            if (obj.IsSetGroupMenuID)
            {
                DalTools.addDbDataParameter(command, "GroupMenuID", obj.GroupMenuID, DbType.String);
            }
            if (obj.IsSetUnitID1)
            {
                DalTools.addDbDataParameter(command, "UnitID1", obj.UnitID1, DbType.String);
            }
            if (obj.IsSetUnitID2)
            {
                DalTools.addDbDataParameter(command, "UnitID2", obj.UnitID2, DbType.String);
            }
            if (obj.IsSetNumber)
            {
                DalTools.addDbDataParameter(command, "Number", obj.Number, DbType.Double);
            }
            if (obj.IsSetPrVAT)
            {
                DalTools.addDbDataParameter(command, "PrVAT", obj.PrVAT, DbType.Double);
            }
            if (obj.IsSetPrMadeIn)
            {
                DalTools.addDbDataParameter(command, "PrMadeIn", obj.PrMadeIn, DbType.String);
            }
            if (obj.IsSetPrPrice_Purchase)
            {
                DalTools.addDbDataParameter(command, "PrPrice_Purchase", obj.PrPrice_Purchase, DbType.Decimal);
            }
            if (obj.IsSetPrPrice_AfterVAT)
            {
                DalTools.addDbDataParameter(command, "PrPrice_AfterVAT", obj.PrPrice_AfterVAT, DbType.Decimal);
            }
            if (obj.IsSetPrPrice_RetailSale)
            {
                DalTools.addDbDataParameter(command, "PrPrice_RetailSale", obj.PrPrice_RetailSale, DbType.Decimal);
            }
            if (obj.IsSetPrPrice_WholeSale)
            {
                DalTools.addDbDataParameter(command, "PrPrice_WholeSale", obj.PrPrice_WholeSale, DbType.Decimal);
            }
            if (obj.IsSetNote)
            {
                DalTools.addDbDataParameter(command, "Note", obj.Note, DbType.String);
            }
            if (obj.IsSetUserNameCreate)
            {
                DalTools.addDbDataParameter(command, "UserNameCreate", obj.UserNameCreate, DbType.String);
            }
            if (obj.IsSetDateCreate)
            {
                DalTools.addDbDataParameter(command, "DateCreate", obj.DateCreate, DbType.DateTime);
            }
            if (obj.IsSetIDPOS)
            {
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);
            }
            if (obj.IsSetIsUse)
            {
                DalTools.addDbDataParameter(command, "IsUse", obj.IsUse, DbType.Boolean);
            }
            if (obj.IsSetIsDelete)
            {
                DalTools.addDbDataParameter(command, "IsDelete", obj.IsDelete, DbType.Boolean);
            }
        }
        #endregion
    }
}